<?php

/**
 * Simple excel generating from PHP5
 *
 * @package Utilities
 * @license http://www.opensource.org/licenses/mit-license.php
 * @author Oliver Schwarz <oliver.schwarz@gmail.com>
 * @version 1.0
 */

/**
 * Generating excel documents on-the-fly from PHP5
 * 
 * Uses the excel XML-specification to generate a native
 * XML document, readable/processable by excel.
 * 
 * @package Utilities
 * @subpackage Excel
 * @author Oliver Schwarz <oliver.schwarz@vaicon.de>
 * @version 1.1
 * 
 * @todo Issue #4: Internet Explorer 7 does not work well with the given header
 * @todo Add option to give out first line as header (bold text)
 * @todo Add option to give out last line as footer (bold text)
 * @todo Add option to write to file
 */
class PHPExcel {

  /**
   * Header (of document)
   * @var string
   */
  //private $header = "<?xml version=\"1.0\" encoding=\"%s\"?\>\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">";
  private $header = "<?xml version=\"1.0\" encoding=\"%s\"?\>\n<?mso-application progid=\"Excel.Sheet\"?><Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">";

  /**
   * Footer (of document)
   * @var string
   */
  private $footer = "</Workbook>";
  /**
   * Lines to output in the excel document
   * @var array
   */
  private $lines = array();
  /**
   * hojas del libro
   * @var array
   */
  private $sheets = array();
  /**
   * Used encoding
   * @var string
   */
  private $sEncoding;
  /**
   * Convert variable types
   * @var boolean
   */
  private $bConvertTypes;

  /**
   * Constructor
   *
   * The constructor allows the setting of some additional
   * parameters so that the library may be configured to
   * one's needs.
   *
   * On converting types:
   * When set to true, the library tries to identify the type of
   * the variable value and set the field specification for Excel
   * accordingly. Be careful with article numbers or postcodes
   * starting with a '0' (zero)!
   *
   * @param string $sEncoding Encoding to be used (defaults to UTF-8)
   * @param boolean $bConvertTypes Convert variables to field specification
   */
  public function __construct($sEncoding = 'UTF-8', $bConvertTypes = false) {
    $this->bConvertTypes = $bConvertTypes;
    $this->setEncoding($sEncoding);
  }

  /**
   * Set encoding
   * @param string Encoding type to set
   */
  public function setEncoding($sEncoding) {
    $this->sEncoding = $sEncoding;
  }

  /**
   * Set worksheet title
   *
   * Strips out not allowed characters and trims the
   * title to a maximum length of 31.
   *
   * @param string $title Title for worksheet
   */
  public function setWorksheetTitle($title) {
    $title = preg_replace("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
    $title = substr($title, 0, 31);
    return $title;
  }

  /**
   * Add row
   *
   * Adds a single row to the document. If set to true, self::bConvertTypes
   * checks the type of variable and returns the specific field settings
   * for the cell.
   *
   * @param string $nameSheet nombre de la hoja
   * @param array $array One-dimensional array with row content
   */
  private function addRow($nameSheet, $array) {
    $cells = "";
    foreach ($array as $k => $v):
      $type = 'String';
      if ($this->bConvertTypes === true && is_numeric($v)):
        $type = 'Number';
      endif;
      $v = htmlentities($v, ENT_COMPAT, $this->sEncoding);
      $cells .= "<Cell><Data ss:Type=\"$type\">" . $v . "</Data></Cell>\n";
    endforeach;
    $this->lines[$nameSheet][] = "<Row>\n" . $cells . "</Row>\n";
  }

  /**
   * Add an array to the document
   * @param string $nameSheet nombre de la hoja
   * @param array 2-dimensional array
   */
  public function addArray($nameSheet, $array) {
    if($nameSheet == "") {
      $nameSheet = "Hoja ".(count($this->sheets) + 1);
    }
    
    $this->sheets[] = $this->setWorksheetTitle($nameSheet);
    foreach ($array as $k => $v)
      $this->addRow($nameSheet, $v);
  }

  /**
   * Generate the excel file
   * @param string $filename Name of excel file to generate (...xls)
   */
  public function generateXML($filename = 'excel-export') {
    // correct/validate filename
    $filename = preg_replace('/[^aA-zZ0-9\_\-]/', '', $filename);

    // print out document to the browser
    // need to use stripslashes for the damn ">"
    echo stripslashes(sprintf($this->header, $this->sEncoding));

    foreach ($this->sheets as $sheet){
      //construye las hojas del excel
      echo "\n<Worksheet ss:Name=\"" . $sheet . "\">\n<Table>\n";
      foreach ($this->lines[$sheet] as $line)
        echo $line;
      echo "</Table>\n</Worksheet>\n";
    }

    //cierra el nodo del xml
    echo $this->footer;

   // deliver header (as recommended in php manual)
    header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
    header("Content-Disposition: attachment; filename=\"" . $filename . ".xls\"");
  }

}

?>